/**
 * 
 */
package gopi.labs.database.postgresql;

import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.Properties;

import gopi.labs.database.DataModel;
import gopi.labs.exceptions.InvalidDataRecord;

/**
 * @author GS4
 * 
 */
public class HsqldbDataModel extends DataModel {

	public HsqldbDataModel(String name) {
		super(name);
	}

	@Override
	public void readParams() throws IOException {
		this.properties = new Properties();
		InputStream is = new FileInputStream("config.properties");
		properties.load(is);

	}

	@Override
	public void create() throws SQLException, InvalidDataRecord {
		Statement stmt = this.connection.createStatement();
		ResultSet rs = stmt
				.executeQuery("select count(*) from metadata.data_model where name = '"
						+ this.getName()
						+ "' and cloud_name = '"
						+ this.getCloudSpace() + "'");

		if (rs.next() && rs.getInt(1) != 0) {
			throw new InvalidDataRecord(this.getCloudSpace() + "."
					+ this.getName() + " already exists");
		} else {
			rs.close();
			stmt.close();
			stmt = this.connection.createStatement();
			stmt.execute("insert into metadata.data_model (cloud_name, name, password) values('"
					+ this.getCloudSpace()
					+ "','"
					+ this.getName()
					+ "','"
					+ this.getPassword() + "')");
			stmt.close();

			insertUpdateAttributes(this.getAttributes());

		}

		stmt = this.connection.createStatement();
		StringBuffer sqlStmt = new StringBuffer();

		sqlStmt.append("create table " + this.getCloudSpace() + "." + this.getName()
				+ "(datetime varchar(100),");
		int total = this.getColumns().size();
		for (int i = 0; i < total; i++) {
			Columns c = this.getColumns().get(i);

			if (c.getDataType().equalsIgnoreCase("INTEGER")) {
				sqlStmt.append(c.getName() + " " + c.getDataType());
			} else if (c.getDataType().equalsIgnoreCase("DOUBLE")) {
				sqlStmt.append(c.getName() + " " + c.getDataType());
			}else if (c.getDataType().equalsIgnoreCase("VARCHAR")) {
				sqlStmt.append(c.getName() + " " + c.getDataType() + "("
						+ c.getDataLength() + ")");
			}
			
			if (i + 1 != total)
				sqlStmt.append(",");
			else
				sqlStmt.append(")");
		}
		System.out.println(sqlStmt.toString());
		stmt.execute(sqlStmt.toString());
		stmt.close();
		this.connection.commit();
	}

	@Override
	public void alter() throws SQLException, InvalidDataRecord {

		Statement stmt = this.connection.createStatement();
		if (!checkCloudExists(this.getCloudSpace()))
			throw new InvalidDataRecord("Cloud name " + this.getName()
					+ " does not exist");

		if (!checkDataModelExists(this.getName(), this.getCloudSpace()))
			throw new InvalidDataRecord("DataModel  " + this.getCloudSpace()
					+ "." + this.getName() + " does not exist");

		insertUpdateAttributes(this.getAttributes());

		ResultSet rs = stmt
				.executeQuery("select column_name, data_type, character_maximum_length from information_schema.columns where  "
						+ "table_schema = '"
						+ this.getCloudSpace()
						+ "' and table_name = '" + this.getName() + "'");
		
		System.out.println("select column_name, data_type, character_maximum_length from information_schema.columns where  "
						+ "table_schema = '"
						+ this.getCloudSpace()
						+ "' and table_name = '" + this.getName() + "'");
		List<Columns> availColumns = new ArrayList<Columns>();
		while (rs.next()) {
			availColumns.add(new Columns(rs.getString("column_name"), rs
					.getString("data_type"), rs
					.getString("character_maximum_length")));
		}
		stmt.close();
		System.out.println(availColumns);
		List<Columns> currentColumns = this.getColumns();

		for (int i = 0; i < currentColumns.size(); i++) {
			stmt = this.connection.createStatement();
			if (columnListIndex(availColumns, currentColumns.get(i)) == -1) {
				

				if (currentColumns.get(i).getDataType()
						.equalsIgnoreCase("VARCHAR"))
					stmt.execute("alter table " + this.getCloudSpace() + "."
							+ this.getName() + " add column  "
							+ currentColumns.get(i).getName() + " "
							+ currentColumns.get(i).getDataType() + "("
							+ currentColumns.get(i).getDataLength() + ")");
				else 
					stmt.execute("alter table " + this.getCloudSpace() + "."
							+ this.getName() + " add column  "
							+ currentColumns.get(i).getName() + " "
							+ currentColumns.get(i).getDataType());

			}
			else {

				if (currentColumns.get(i).getDataType()
						.equalsIgnoreCase("VARCHAR"))
					stmt.execute("alter table " + this.getCloudSpace() + "."
							+ this.getName() + " alter column  "
							+ currentColumns.get(i).getName() + " "
							+ currentColumns.get(i).getDataType() + "("
							+ currentColumns.get(i).getDataLength() + ")");
				else 
					stmt.execute("alter table " + this.getCloudSpace() + "."
							+ this.getName() + " alter column  "
							+ currentColumns.get(i).getName() + " "
							+ currentColumns.get(i).getDataType());
			}
			stmt.close();
		}
		this.connection.commit();
	}

	private int columnListIndex(List<Columns> cList, Columns c) {
		int index = -1;

		for (int i = 0; i < cList.size(); i++) {
			if (cList.get(i).getName().equalsIgnoreCase(c.getName()))
				return i;
		}
		return index;
	}

	@Override
	public void drop() throws SQLException {
		Statement stmt = this.connection.createStatement();

		stmt.execute("delete from metadata.data_model_attributes where name = '"
				+ this.getName()
				+ "' and cloud_name = '"
				+ this.getCloudSpace() + "'");
		stmt.execute("delete from metadata.data_model where name = '"
				+ this.getName() + "' and cloud_name ='" + this.getCloudSpace()
				+ "'");
		stmt.execute("delete from metadata.data_stream_attributes where data_model_name = '"
				+ this.getName() + "' and cloud_name ='" + this.getCloudSpace()
				+ "'");
		stmt.execute("delete from metadata.data_stream where data_model_name = '"
				+ this.getName() + "' and cloud_name ='" + this.getCloudSpace()
				+ "'");
		stmt.execute("drop table " + this.getCloudSpace() + "."
				+ this.getName());
		stmt.close();
		this.connection.commit();
	}

}
